using System;
using System.Data;
using System.Data.SqlClient;
using PetShop.BusinessEntities;
using PetShop.DataAccess.Utilities;

namespace PetShop.DataAccess.DAO
{
    public class AccountDAO
    {
        private const string SQL_SELECT_ADDRESS =
            "SELECT Account.FirstName, Account.LastName, Account.Addr1, Account.Addr2, Account.City, Account.State, Account.Zip, Account.Country, Account.Phone FROM Account WHERE Account.UserId = @UserId";

        private const string PARM_USER_ID = "@UserId";
        private const string PARM_PASSWORD = "@Password";
        private const string PARM_EMAIL = "@Email";
        private const string PARM_FIRST_NAME = "@FirstName";
        private const string PARM_LAST_NAME = "@LastName";
        private const string PARM_ADDRESS1 = "@Address1";
        private const string PARM_ADDRESS2 = "@Address2";
        private const string PARM_CITY = "@City";
        private const string PARM_STATE = "@State";
        private const string PARM_ZIP = "@Zip";
        private const string PARM_COUNTRY = "@Country";
        private const string PARM_PHONE = "@Phone";
        private const string PARM_LANGUAGE = "@Language";
        private const string PARM_CATEGORY = "@Category";
        private const string PARM_SHOW_FAVORITES = "@ShowFavorites";
        private const string PARM_SHOW_BANNERS = "@ShowBanners";

        private const string SQL_INSERT_ACCOUNT = "INSERT INTO Account " +
                                                  " VALUES(@UserId, @Email, @FirstName, @LastName,  @Address1, @Address2," +
                                                  " @City, @State, @Zip, @Country, @Phone,@Language, @Category, @ShowFavorites, " +
                                                  " @ShowBanners, @Password)";

        private const string SQL_SELECT_ACCOUNT = "SELECT Email," +
                                                  "FirstName, LastName, Addr1, " +
                                                  " Addr2, City, State, Zip, " +
                                                  " Country, Phone, " +
                                                  " LangPref, FavCategory, MyListOpt, " +
                                                  " BannerOpt " +
                                                  " FROM Account " +
                                                  " WHERE UserId = @UserId AND Password = @Password";

        private const string SQL_UPDATE_ACCOUNT = "UPDATE Account " +
                                                  " SET Email = @Email, FirstName = @FirstName, LastName = @LastName, " +
                                                  " Addr1 = @Address1, Addr2 = @Address2, City = @City, State = @State, " +
                                                  " Zip = @Zip, Country = @Country, Phone = @Phone ,LangPref = @Language, FavCategory = @Category, MyListOpt = @ShowFavorites, BannerOpt = @ShowBanners " +
                                                  " WHERE UserId = @UserId";

        public AccountInfo GetAccountInfo(string userId, string password)
        {
            SqlParameter[] accountInfoParms = GetAccountInfoParameters();

            accountInfoParms[0].Value = userId;
            accountInfoParms[1].Value = password;

            using (
                SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnString, CommandType.Text, SQL_SELECT_ACCOUNT,
                                                            accountInfoParms))
            {
                if (rdr.Read())
                {
                    var myAddress = new AddressInfo(rdr.GetString(1), rdr.GetString(2), rdr.GetString(3),
                                                    rdr.GetString(4), rdr.GetString(5), rdr.GetString(6),
                                                    rdr.GetString(7), rdr.GetString(8), rdr.GetString(9));
                    return new AccountInfo(userId, password, rdr.GetString(0), myAddress, rdr.GetString(10),
                                           rdr.GetString(11), Convert.ToBoolean(rdr.GetInt32(12)),
                                           Convert.ToBoolean(rdr.GetInt32(13)));
                }
                return null;
            }
        }


        public void Insert(AccountInfo acc)
        {
            SqlParameter[] accountParms = GetInsertAccountParameters();
            SetInsertAccountParameters(accountParms, acc);
            using (var conn = new SqlConnection(SQLHelper.ConnString))
            {
                conn.Open();
                SQLHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_ACCOUNT, accountParms);
            }
        }


        public void Update(AccountInfo myAccount)
        {
            SqlParameter[] accountParms = GetUpdateAccountParameters();


            SetUpdateAccountParameters(accountParms, myAccount);


            using (var conn = new SqlConnection(SQLHelper.ConnString))
            {
                conn.Open();
                SQLHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_ACCOUNT, accountParms);
            }
        }


        private static SqlParameter[] GetAccountInfoParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_SELECT_ACCOUNT);

            if (parms == null)
            {
                parms = new[]
                            {
                                new SqlParameter(PARM_USER_ID, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_PASSWORD, SqlDbType.VarChar, 80)
                            };

                SQLHelper.CacheParameters(SQL_SELECT_ACCOUNT, parms);
            }

            return parms;
        }

        private static SqlParameter[] GetInsertAccountParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT_ACCOUNT);

            if (parms == null)
            {
                parms = new[]
                            {
                                new SqlParameter(PARM_EMAIL, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_FIRST_NAME, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_LAST_NAME, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_ADDRESS1, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_ADDRESS2, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_CITY, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_STATE, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_ZIP, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_COUNTRY, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_PHONE, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_USER_ID, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_LANGUAGE, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_CATEGORY, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_SHOW_FAVORITES, SqlDbType.Int, 4),
                                new SqlParameter(PARM_SHOW_BANNERS, SqlDbType.Int, 4),
                                new SqlParameter(PARM_PASSWORD, SqlDbType.VarChar, 80)
                            };

                SQLHelper.CacheParameters(SQL_INSERT_ACCOUNT, parms);
            }

            return parms;
        }


        private static SqlParameter[] GetUpdateAccountParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_UPDATE_ACCOUNT);

            if (parms == null)
            {
                parms = new[]
                            {
                                new SqlParameter(PARM_EMAIL, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_FIRST_NAME, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_LAST_NAME, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_ADDRESS1, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_ADDRESS2, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_CITY, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_STATE, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_ZIP, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_COUNTRY, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_PHONE, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_USER_ID, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_LANGUAGE, SqlDbType.VarChar, 80),
                                new SqlParameter(PARM_CATEGORY, SqlDbType.VarChar, 50),
                                new SqlParameter(PARM_SHOW_FAVORITES, SqlDbType.Int, 4),
                                new SqlParameter(PARM_SHOW_BANNERS, SqlDbType.Int, 4)
                            };

                SQLHelper.CacheParameters(SQL_UPDATE_ACCOUNT, parms);
            }

            return parms;
        }


        private void SetInsertAccountParameters(SqlParameter[] parms, AccountInfo acc)
        {
            parms[0].Value = acc.Email;
            parms[1].Value = acc.Address.FirstName;
            parms[2].Value = acc.Address.LastName;
            parms[3].Value = acc.Address.Address1;
            parms[4].Value = acc.Address.Address2;
            parms[5].Value = acc.Address.City;
            parms[6].Value = acc.Address.State;
            parms[7].Value = acc.Address.Zip;
            parms[8].Value = acc.Address.Country;
            parms[9].Value = acc.Address.Phone;
            parms[10].Value = acc.UserId;
            parms[11].Value = acc.Language;
            parms[12].Value = acc.Category;
            parms[13].Value = acc.IsShowFavorites;
            parms[14].Value = acc.IsShowBanners;
            parms[15].Value = acc.Password;
        }


        private void SetUpdateAccountParameters(SqlParameter[] parms, AccountInfo acc)
        {
            parms[0].Value = acc.Email;
            parms[1].Value = acc.Address.FirstName;
            parms[2].Value = acc.Address.LastName;
            parms[3].Value = acc.Address.Address1;
            parms[4].Value = acc.Address.Address2;
            parms[5].Value = acc.Address.City;
            parms[6].Value = acc.Address.State;
            parms[7].Value = acc.Address.Zip;
            parms[8].Value = acc.Address.Country;
            parms[9].Value = acc.Address.Phone;
            parms[10].Value = acc.UserId;
            parms[11].Value = acc.Language;
            parms[12].Value = acc.Category;
            parms[13].Value = acc.IsShowFavorites;
            parms[14].Value = acc.IsShowBanners;
        }
    }
}